<?php


namespace app\home\controller;


use app\base\BaseController;
use think\facade\Db;
use think\facade\View;

/**
 * Class CwKc
 * @package app\home\controller
 * 财务库存
 */
class CwKc extends BaseController
{
    private $sessionKey='pageCwKc';
    protected $title='财务库存';
    /**
     * @autho:xiangzi
     * @auth:true
     * @Date:2024/7/22
     */
    public function index()
    {
        if (request()->isAjax()) {
            $param=$this->request->param();
            $rows = empty($param['limit']) ? get_config('app.page_size') : $param['limit'];

            $where[]=['ird.status','=',2];
            if (isset($param['month'])&&$param['month']){
                $where[]=['ir.incomTime','like',$param['month'].'%'];
            }
            if (isset($param['warehouse_id'])&&$param['warehouse_id']){
                $where[]=['ird.warehouse_id','=',$param['warehouse_id']];
            }
            if (isset($param['bar_code'])&&$param['bar_code']){
                $where[]=['md.bar_code','like','%'.$param['bar_code'].'%'];
            }
            if (isset($param['mdName'])&&$param['mdName']){
                $where[]=['md.name','like','%'.$param['mdName'].'%'];
            }
            session($this->sessionKey,$where);
            $list=Db::name('in_receipt_detail')->alias('ird')
                ->leftJoin('in_receipt ir','ird.in_receipt_id=ir.id')
                ->leftJoin('material_dossier md','md.id=ird.material_dossier_id')
                ->leftJoin('warehouse w','w.id=ird.warehouse_id')
                ->where($where)
                ->field("ir.incomTime,ird.*,md.name as mdName,md.bar_code,DATE_FORMAT(ir.incomTime, '%Y-%m') as inMonth,w.name as wName")
                ->order("incomTime DESC")
                ->group("DATE_FORMAT(ir.incomTime, '%Y-%m'),md.id")
                ->paginate($rows)
                ->each(function ($item){
//                    $s_time=strtotime($item['inMonth'].'-01');
//                    $e_time=strtotime(date('Y-m-t 23:59:59',strtotime($item['inMonth'])));
                    $wherePrice=[];
                    $wherePrice[]=['status','=',1];
                    $wherePrice[]=['p_type','=',1];
                    $wherePrice[]=['time','=',$item['inMonth']];
                    $wherePrice[]=['material_dossier_id','=',$item['material_dossier_id']];
                    $countPrice=Db::name('purveyor_price')->where($wherePrice)->sum('price');
                    $countNum=Db::name('purveyor_price')->where($wherePrice)->count();
                    $dj=0;
                    if ($countNum){
                        $dj=round($countPrice/$countNum,2);
                    }
                    $item['dj']=$dj;
                    $ruku=Db::name('in_receipt_detail')->alias('ird')
                        ->leftJoin('in_receipt ir','ir.id=ird.in_receipt_id')
                        ->where('ird.status',2)
                        ->where('material_dossier_id',$item['material_dossier_id'])
                        ->where('ir.incomTime','<',$item['inMonth'].'-01')->sum('ird.num');

                    $chuku=Db::name('out_receipt_detail')->alias('ord')
                        ->leftJoin('out_receipt or','or.id=ord.out_receipt_id')
                        ->where('material_dossier_id',$item['material_dossier_id'])
                        ->where('ord.status',2)
                        ->where('or.outTime','<',$item['inMonth'])->sum('ord.num');
                    $byqichu=$ruku-$chuku;
                    $item['byqichu']=$byqichu;
                    $item['byqcMoney']=round(($dj*$byqichu),2);
                    $byrk=Db::name('in_receipt_detail')->alias('ird')
                        ->leftJoin('in_receipt ir','ir.id=ird.in_receipt_id')
                        ->where('ird.status',2)
                        ->where('ird.material_dossier_id',$item['material_dossier_id'])
                        ->where('ir.incomTime','like',$item['inMonth'].'%')->sum('ird.num');
                    $byck=Db::name('out_receipt_detail')->alias('ord')
                        ->leftJoin('out_receipt or','or.id=ord.out_receipt_id')
                        ->where('material_dossier_id',$item['material_dossier_id'])
                        ->where('ord.status',2)
                        ->where('or.outTime','like',$item['inMonth'].'%')->sum('ord.num');
                    $item['byru']=$byrk;
                    $item['byck']=$byck;
                    $item['byrkMoney']=round(($dj*$byrk),2);
                    $item['byckMoney']=round(($dj*$byck),2);
                    $byjc=$byqichu+$byrk-$byck;
                    $item['byjc']=$byjc;
                    $item['byjcMoney']=round(($dj*$byjc),2);
                    return $item;
                });
            return table_assign(0,'',$list);
        }
        $wList=Db::name('warehouse')->where('status',1)->field('id,name')->select();
        View::assign('wList',$wList);
       return view();
}

    public function exportData()
    {
        $where=session($this->sessionKey);
        $list=Db::name('in_receipt_detail')->alias('ird')
            ->leftJoin('in_receipt ir','ird.in_receipt_id=ir.id')
            ->leftJoin('material_dossier md','md.id=ird.material_dossier_id')
            ->leftJoin('warehouse w','w.id=ird.warehouse_id')
            ->where($where)
            ->field("ir.incomTime,ird.*,md.name as mdName,md.bar_code,DATE_FORMAT(ir.incomTime, '%Y-%m') as inMonth,w.name as wName")
            ->order("incomTime DESC")
            ->group("DATE_FORMAT(ir.incomTime, '%Y-%m'),md.id")
            ->select()
            ->each(function ($item){
//                    $s_time=strtotime($item['inMonth'].'-01');
//                    $e_time=strtotime(date('Y-m-t 23:59:59',strtotime($item['inMonth'])));
                $wherePrice=[];
                $wherePrice[]=['status','=',1];
                $wherePrice[]=['p_type','=',1];
                $wherePrice[]=['time','=',$item['inMonth']];
                $wherePrice[]=['material_dossier_id','=',$item['material_dossier_id']];
                $countPrice=Db::name('purveyor_price')->where($wherePrice)->sum('price');
                $countNum=Db::name('purveyor_price')->where($wherePrice)->count();
                $dj=0;
                if ($countNum){
                    $dj=round($countPrice/$countNum,2);
                }
                $item['dj']=$dj;
                $ruku=Db::name('in_receipt_detail')->alias('ird')
                    ->leftJoin('in_receipt ir','ir.id=ird.in_receipt_id')
                    ->where('ird.status',2)
                    ->where('material_dossier_id',$item['material_dossier_id'])
                    ->where('ir.incomTime','<',$item['inMonth'].'-01')->sum('ird.num');

                $chuku=Db::name('out_receipt_detail')->alias('ord')
                    ->leftJoin('out_receipt or','or.id=ord.out_receipt_id')
                    ->where('material_dossier_id',$item['material_dossier_id'])
                    ->where('ord.status',2)
                    ->where('or.outTime','<',$item['inMonth'])->sum('ord.num');
                $byqichu=$ruku-$chuku;
                $item['byqichu']=$byqichu;
                $item['byqcMoney']=round(($dj*$byqichu),2);
                $byrk=Db::name('in_receipt_detail')->alias('ird')
                    ->leftJoin('in_receipt ir','ir.id=ird.in_receipt_id')
                    ->where('ird.status',2)
                    ->where('ird.material_dossier_id',$item['material_dossier_id'])
                    ->where('ir.incomTime','like',$item['inMonth'].'%')->sum('ird.num');
                $byck=Db::name('out_receipt_detail')->alias('ord')
                    ->leftJoin('out_receipt or','or.id=ord.out_receipt_id')
                    ->where('material_dossier_id',$item['material_dossier_id'])
                    ->where('ord.status',2)
                    ->where('or.outTime','like',$item['inMonth'].'%')->sum('ord.num');
                $item['byru']=$byrk;
                $item['byck']=$byck;
                $item['byrkMoney']=round(($dj*$byrk),2);
                $item['byckMoney']=round(($dj*$byck),2);
                $byjc=$byqichu+$byrk-$byck;
                $item['byjc']=$byjc;
                $item['byjcMoney']=round(($dj*$byjc),2);
                return $item;
            });
        $title = [
//            'id' => 'ID',
            'inMonth' => '月份',
            'wName' => '仓库',
            'bar_code' => '物料编码',
            'mdName' => '物料名称',
            'dj' => '单价',
            'byqichu' => '本月期初数量',
            'byqcMoney' => '本月期初金额',
            'byru' => '本月入库数量',
            'byrkMoney' => '本月入库金额',
            'byck' => '本月出库数量',
            'byckMoney' => '本月出库金额',
            'byjc' => '本月结存数量',
            'byjcMoney' => '本月结存金额',
        ];
        $name =$this->title;
        exportXlsData($name,$title,$list);
        exit();
    }
}